package com.dy.yunying.biz.dao.datacenter.impl;

import cn.hutool.core.collection.CollectionUtil;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.dy.yunying.api.constant.Constant;
import com.dy.yunying.api.datacenter.vo.PlanAttrAnalyseSearchVo;
import com.dy.yunying.api.datacenter.vo.PlanBaseAttrVo;
import com.dy.yunying.biz.config.YunYingProperties;
import com.pig4cloud.pig.common.core.constant.enums.PlanAttrStatTypeEnum;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.List;
import java.util.Objects;
import java.util.StringJoiner;

@Slf4j
@Component(value = "dcAdPlanAttrStatDao")
public class AdPlanAttrStatDao {

	@Resource(name = "clickDcSessionTemplate")
	private JdbcTemplate clickhouseTemplate;

	@Resource
	private YunYingProperties yunYingProperties;

	/**
	 * 统计结果记录条数
	 */
	public Long countDataTotal(PlanAttrAnalyseSearchVo req) {
		final String indentStr = "        ";
		StringBuilder countSql = new StringBuilder();
		countSql.append("SELECT\n");
		countSql.append("    COUNT(1)\n");
		countSql.append("FROM\n");
		countSql.append("    (\n");
		countSql.append(this.getSql(req, indentStr));
		countSql.append("    )\n");
		log.debug("countSql : [\n{}]", countSql.toString());

		long start = System.currentTimeMillis();
		Long count = clickhouseTemplate.queryForObject(countSql.toString(), Long.class);
		long end = System.currentTimeMillis();
		log.info("计划属性分析表总数查询耗时: {}ms", end - start);

		return count;
	}

	public List<PlanBaseAttrVo> selectPlanAttrAnalyseReoport(PlanAttrAnalyseSearchVo req) {
		final String indentStr = StringUtils.EMPTY;
		final List<String> queryColumn = req.getQueryColumn();
		final StringBuilder sql = this.getSql(req, indentStr);

		// 处理汇总，计划属性报表汇总
		if (req.getCycleType() != 4) {
			// 排序
			String kpiValue = req.getKpiValue();
			String sort = req.getSort();
			if (StringUtils.isNotBlank(kpiValue) && StringUtils.isNotBlank(sort)) {
				sql.append("ORDER BY\n");
				sql.append("    ").append(kpiValue).append(" ").append(sort).append('\n');
			} else {
				sql.append("ORDER BY\n");
				sql.append("    ").append("day DESC");
				if (CollectionUtils.isNotEmpty(queryColumn) && queryColumn.contains(PlanAttrStatTypeEnum.ADID.V())) {
					sql.append(", createTime DESC");
				}
				sql.append('\n');
			}

			// 分页
			Long current = req.getCurrent();
			Long size = req.getSize();
			if (Objects.nonNull(current) && Objects.nonNull(size)) {
				Long offset = (current - 1) * size;
				sql.append("LIMIT\n");
				sql.append("    ").append(offset).append(", ").append(size).append('\n');
			}

		}
		log.debug("计划属性分析表分页查询SQL: [\n{}]", sql.toString());

		long start = System.currentTimeMillis();
		List<PlanBaseAttrVo> list = clickhouseTemplate.query(sql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(PlanBaseAttrVo.class));
		long end = System.currentTimeMillis();
		log.info("计划属性分析表分页查询耗时: {}", end - start);

		return list;
	}


	public StringBuilder getSql(PlanAttrAnalyseSearchVo req, final String indentStr) {
		// 临时调整以下内容暂不处理过滤条件,后期需要补上
		final StringBuilder sql = new StringBuilder();
		List<String> queryColumns = req.getQueryColumn();
		final String groupByColumn = this.convertGroupByColumns(queryColumns);

		//处理汇总
		if (req.getCycleType() == 4) {
			sql.append(indentStr).append("SELECT\n");
			sql.append(indentStr).append("    round(COALESCE(SUM(rudeCosts), 0), 2) rudeCost, -- 原始消耗\n");
			sql.append(indentStr).append("    round(COALESCE(SUM(costs), 0), 2) cost, -- 返点后消耗\n");
			sql.append(indentStr).append("    COALESCE(SUM(usrnamenum), 0) usrnamenums, -- 新增设备注册数\n");
			sql.append(indentStr).append("    COALESCE(SUM(newUserCount), 0) AS newUserCount, -- 新增用户数\n");
			sql.append(indentStr).append("    COALESCE(SUM(createRoleCount), 0) AS createRoleCount, -- 新增注册创角数\n");
			sql.append(indentStr).append("    COALESCE(SUM(certifiedCount), 0) AS certifiedCount, -- 新增注册实名数\n");
			sql.append(indentStr).append("    COALESCE(SUM(notCertifiedCount), 0) AS notCertifiedCount, -- 注册未实名数\n");
			sql.append(indentStr).append("    COALESCE(SUM(youngCount), 0) AS youngCount, -- 未成年人数\n");
			sql.append(indentStr).append("    COALESCE(SUM(usrpaynamenum), 0) paydevice1, -- 新增设备付费数\n");
			sql.append(indentStr).append("    round(COALESCE(SUM(newdevicefee), 0), 2) newdevicefees, -- 新增充值实付金额（分成前）\n");
			sql.append(indentStr).append("    round(COALESCE(SUM(newdevicesharfees), 0), 2) worth1, -- 新增充值实付金额（分成后）\n");
			sql.append(indentStr).append("    round(COALESCE(SUM(newdevicegivemoney), 0), 2) newdevicegivemoney, -- 新增充值代金券金额（分成前）\n");
			sql.append(indentStr).append("    round(COALESCE(SUM(newdeviceshargivemoney), 0), 2) newdeviceshargivemoney, -- 新增充值代金券金额（分成后）\n");
			sql.append(indentStr).append("    round(COALESCE(SUM(totalPayfees), 0), 2) totalPayfee, -- 累计充值实付金额（分成前）\n");
			sql.append(indentStr).append("    round(COALESCE(SUM(totalPaysharfees), 0), 2) userfeeAll, -- 累计充值实付金额（分成后）\n");
			sql.append(indentStr).append("    round(COALESCE(SUM(totalPaygivemoney), 0), 2) totalPaygivemoney, -- 累计充值代金券金额（分成前）\n");
			sql.append(indentStr).append("    round(COALESCE(SUM(totalPayshargivemoney), 0), 2) totalPayshargivemoney, -- 累计充值代金券金额（分成后）\n");
			sql.append(indentStr).append("    COALESCE(SUM(retention2s), 0) retention2, -- 次留\n");
			sql.append(indentStr).append("    round(IF(toInt64(SUM(usrnamenum)) > 0, divide(toFloat64(SUM(costs)), SUM(usrnamenum)), 0), 2) deviceCose,-- 设备成本\n");
			sql.append(indentStr).append("    round(IF(toInt64(SUM(usrnamenum)) > 0, divide(SUM(usrpaynamenum) * 100.00, SUM(usrnamenum)), 0), 2) regPayRatio, -- 新增付费率\n");
			sql.append(indentStr).append("    round(IF(SUM(costs) > 0, divide(SUM(newdevicesharfees) * 100.00, SUM(costs)), 0), 2) roi1, -- 首日Roi 新增充值金额/返点后消耗\n");
			sql.append(indentStr).append("    round(IF(toInt64(SUM(usrnamenum)) > 0, divide(SUM(retention2s) * 100.00, SUM(usrnamenum)), 0), 2) retention2Ratio, -- 次留\n");
			sql.append(indentStr).append("    round(IF(SUM(costs) > 0, divide(SUM(totalPaysharfees) * 100.00, SUM(costs)), 0), 2) allRoi, -- 累计Roi 累计充值金额/返点后消耗\n");
			sql.append(indentStr).append("    round(IF(SUM(usrnamenum) > 0, divide(SUM(newdevicesharfees), SUM(usrnamenum)), 0), 2) firstDayLtv, -- 首日LTV\n");
			sql.append(indentStr).append("    round(IF(toInt64(paydevice1) > 0, divide(toFloat64(worth1), paydevice1), 0), 2) AS newPayArppu, -- 新增付费ARPPU\n");
			sql.append(indentStr).append("    round(IF(toInt64(usrnamenums) > 0, divide(createRoleCount * 100.00, usrnamenums), 0), 2) AS createRoleRate, -- 新增注册创角率\n");
			sql.append(indentStr).append("    round(IF(toInt64(usrnamenums) > 0, divide(certifiedCount * 100.00, usrnamenums), 0), 2) AS certifiedRate -- 新增实名转化率\n");
		} else {
			sql.append(indentStr).append("SELECT\n");
			sql.append(indentStr).append("    day, id, ");
			if (CollectionUtils.isNotEmpty(queryColumns) && queryColumns.contains(PlanAttrStatTypeEnum.ADID.V())) {
				sql.append("adAccountId, adAccountName, convertName, deepConvert, createTime, ");
			}
			if (CollectionUtils.isNotEmpty(queryColumns) && (queryColumns.contains(PlanAttrStatTypeEnum.ADID.V()) || queryColumns.contains(PlanAttrStatTypeEnum.ADVERTISERID.V()))) {
				sql.append("ctype, ");
			}
			if (CollectionUtils.isNotEmpty(queryColumns) && queryColumns.contains(PlanAttrStatTypeEnum.APPCHL.V())) {
				sql.append("id name, ");
			} else {
				sql.append("name, ");
			}
			if (CollectionUtils.isNotEmpty(queryColumns) && queryColumns.contains("investor")) {
				sql.append("IFNULL(osu.real_name, '') investorName, ");
			}
			sql.append('\n');

			sql.append(indentStr).append("    round(COALESCE(rudeCosts, 0), 2) rudeCost, -- 原始消耗\n");
			sql.append(indentStr).append("    round(COALESCE(costs, 0), 2) cost, -- 返点后消耗\n");
			sql.append(indentStr).append("    COALESCE(usrnamenum, 0) usrnamenums, -- 新增设备注册数\n");
			sql.append(indentStr).append("    newUserCount, -- 新增用户数\n");
			sql.append(indentStr).append("    createRoleCount, -- 新增注册创角数\n");
			sql.append(indentStr).append("    certifiedCount, -- 新增注册实名数\n");
			sql.append(indentStr).append("    notCertifiedCount, -- 注册未实名数\n");
			sql.append(indentStr).append("    youngCount, -- 未成年人数\n");
			sql.append(indentStr).append("    COALESCE(usrpaynamenum, 0) paydevice1, -- 新增设备付费数\n");
			sql.append(indentStr).append("    round(COALESCE(newdevicefee, 0), 2) newdevicefees, -- 新增充值实付金额（分成前）\n");
			sql.append(indentStr).append("    round(COALESCE(newdevicesharfees, 0), 2) worth1, -- 新增充值实付金额（分成后）\n");
			sql.append(indentStr).append("    round(COALESCE(newdevicegivemoney, 0), 2) newdevicegivemoney, -- 新增充值代金券金额（分成前）\n");
			sql.append(indentStr).append("    round(COALESCE(newdeviceshargivemoney, 0), 2) newdeviceshargivemoney, -- 新增充值代金券金额（分成后）\n");
			sql.append(indentStr).append("    round(COALESCE(totalPayfees, 0), 2) totalPayfee, -- 累计充值实付金额（分成前）\n");
			sql.append(indentStr).append("    round(COALESCE(totalPaysharfees, 0), 2) userfeeAll, -- 累计充值实付金额（分成后）\n");
			sql.append(indentStr).append("    round(COALESCE(totalPaygivemoney, 0), 2) totalPaygivemoney, -- 累计充值代金券金额（分成前）\n");
			sql.append(indentStr).append("    round(COALESCE(totalPayshargivemoney, 0), 2) totalPayshargivemoney, -- 累计充值代金券金额（分成后）\n");
			sql.append(indentStr).append("    COALESCE(retention2s,0) retention2, -- 次留\n");
			// 比率指标
			sql.append(indentStr).append("    round(if(toInt64(usrnamenum) > 0, divide(toFloat64(costs), usrnamenum),0),2) deviceCose, -- 设备成本\n");
			sql.append(indentStr).append("    round(if(toInt64(usrnamenum) > 0, divide(usrpaynamenum * 100.00, usrnamenum),0),2) regPayRatio, -- 新增付费率\n");
			sql.append(indentStr).append("    round(if(costs > 0, divide(newdevicesharfees * 100.00,costs), 0), 2) roi1, -- 首日Roi 新增充值金额/返点后消耗\n");
			sql.append(indentStr).append("    round(if(toInt64(usrnamenum) > 0, divide(retention2s * 100.00, usrnamenum), 0), 2) retention2Ratio, -- 次留\n");
			sql.append(indentStr).append("    round(if(costs > 0, divide(totalPaysharfees * 100.00, costs), 0), 2) allRoi, -- 累计Roi 累计充值金额/返点后消耗\n");
			sql.append(indentStr).append("    round(if(usrnamenum > 0, divide(newdevicesharfees, usrnamenum), 0), 2) firstDayLtv, -- 首日LTV\n");
			sql.append(indentStr).append("    round(IF(toInt64(paydevice1) > 0, divide(toFloat64(worth1), paydevice1), 0), 2) AS newPayArppu, -- 新增付费ARPPU\n");
			sql.append(indentStr).append("    round(IF(toInt64(usrnamenums) > 0, divide(createRoleCount * 100.00, usrnamenums), 0), 2) AS createRoleRate, -- 新增注册创角率\n");
			sql.append(indentStr).append("    round(IF(toInt64(usrnamenums) > 0, divide(certifiedCount * 100.00, usrnamenums), 0), 2) AS certifiedRate -- 新增实名转化率\n");
		}
		sql.append(indentStr).append("FROM\n");
		sql.append(indentStr).append("    (\n");
		sql.append(indentStr).append("        SELECT\n");
		sql.append(indentStr).append("            ").append(groupByColumn).append(", ").append(CollectionUtils.isNotEmpty(queryColumns) ? queryColumns.get(0) + " AS id," : StringUtils.EMPTY).append('\n');
		sql.append(indentStr).append("            toFloat64(rudeCost) rudeCosts, --原始消耗 \n");
		sql.append(indentStr).append("            toFloat64(cost) costs, --返点后消耗 \n");
		sql.append(indentStr).append("            COALESCE(usrnamenums, 0) usrnamenum, --新增设备注册数\n");
		sql.append(indentStr).append("            COALESCE(usrpaynamenums, 0) usrpaynamenum, --新增设备付费数\n");
		sql.append(indentStr).append("            toFloat64(newdevicefees) newdevicefee, --新增充值实付金额（分成前）\n");
		sql.append(indentStr).append("            toFloat64(newdevicesharfee) newdevicesharfees, --新增充值实付金额（分成后）\n");
		sql.append(indentStr).append("            toFloat64(newdevicegivemoney) newdevicegivemoney, --新增充值代金券金额（分成前）\n");
		sql.append(indentStr).append("            toFloat64(newdeviceshargivemoney) newdeviceshargivemoney, --新增充值代金券金额（分成后）\n");
		sql.append(indentStr).append("            toFloat64(totalPayfee) totalPayfees, --累计充值实付金额（分成前）\n");
		sql.append(indentStr).append("            toFloat64(totalPaysharfee) totalPaysharfees, --累计充值实付金额（分成后）\n");
		sql.append(indentStr).append("            toFloat64(totalPaygivemoney) totalPaygivemoney, --累计充值代金券金额（分成前）\n");
		sql.append(indentStr).append("            toFloat64(totalPayshargivemoney) totalPayshargivemoney, --累计充值代金券金额（分成后）\n");
		sql.append(indentStr).append("            COALESCE(retention2, 0) retention2s, -- 次留\n");
		sql.append(indentStr).append("            newUserCount, -- 新增用户数\n");
		sql.append(indentStr).append("            createRoleCount, -- 新增注册创角数\n");
		sql.append(indentStr).append("            certifiedCount, -- 新增注册实名数\n");
		sql.append(indentStr).append("            notCertifiedCount, -- 注册未实名数\n");
		sql.append(indentStr).append("            youngCount -- 未成年人数\n");
		sql.append(indentStr).append("        FROM\n");
		sql.append(indentStr).append("            (\n");
		sql.append(indentStr).append("                SELECT\n");
		sql.append(indentStr).append("                    ").append(groupByColumn).append(",\n");
		sql.append(indentStr).append("                    uuidnums,  -- 新增设备注册数\n");
		sql.append(indentStr).append("                    usrnamenums, -- 新增设备注册数\n");
		sql.append(indentStr).append("                    retention2, -- 留存人数\n");
		sql.append(indentStr).append("                    usrpaynamenums, -- 新增设备付费数\n");
		sql.append(indentStr).append("                    newdevicefees, -- 新增充值实付金额（分成前）\n");
		sql.append(indentStr).append("                    newdevicesharfee, -- 新增充值实付金额（分成后）\n");
		sql.append(indentStr).append("                    newdevicegivemoney, -- 新增充值代金券金额（分成前）\n");
		sql.append(indentStr).append("                    newdeviceshargivemoney, -- 新增充值代金券金额（分成后）\n");
		sql.append(indentStr).append("                    weektotalfeenums, -- 当周充值人数\n");
		sql.append(indentStr).append("                    weektotalfee, -- 当周充值实付金额（分成前）\n");
		sql.append(indentStr).append("                    weeksharfee, -- 当周充值实付金额（分成后）\n");
		sql.append(indentStr).append("                    weektotalgivemoney, -- 当周充值代金券金额（分成前）\n");
		sql.append(indentStr).append("                    weekshargivemoney, -- 当周充值代金券金额（分成后）\n");
		sql.append(indentStr).append("                    monthtotalfeenums, -- 当月充值人数\n");
		sql.append(indentStr).append("                    monthtotalfee, -- 当月充值实付金额（分成前）\n");
		sql.append(indentStr).append("                    monthsharfee, -- 当月充值实付金额（分成后）\n");
		sql.append(indentStr).append("                    monthtotalgivemoney, -- 当月充值代金券金额（分成前）\n");
		sql.append(indentStr).append("                    monthshargivemoney, -- 当月充值代金券金额（分成后）\n");
		sql.append(indentStr).append("                    totalPayfeenums, -- 累计充值人数\n");
		sql.append(indentStr).append("                    totalPayfee, -- 累计充值实付金额（分成前）\n");
		sql.append(indentStr).append("                    totalPaysharfee, -- 累计充值实付金额（分成后）\n");
		sql.append(indentStr).append("                    totalPaygivemoney, -- 累计充值代金券金额（分成前）\n");
		sql.append(indentStr).append("                    totalPayshargivemoney, -- 累计充值代金券金额（分成后）\n");
		sql.append(indentStr).append("                    newUserCount, -- 新增用户数\n");
		sql.append(indentStr).append("                    createRoleCount, -- 新增注册创角数\n");
		sql.append(indentStr).append("                    certifiedCount, -- 新增注册实名数\n");
		sql.append(indentStr).append("                    notCertifiedCount, -- 注册未实名数\n");
		sql.append(indentStr).append("                    youngCount -- 未成年人数\n");
		sql.append(indentStr).append("                FROM\n");
		sql.append(indentStr).append("                    (\n");
		sql.append(this.getDevicePaySql(req, indentStr + "                        "));
		sql.append(indentStr).append("                    ) a\n");
		sql.append(indentStr).append("                    FULL JOIN (\n");
		sql.append(this.getCertifiedSql(req, indentStr + "                        "));
		sql.append(indentStr).append("                    ) b USING (").append(groupByColumn).append(")\n");
		sql.append(indentStr).append("            ) c\n");
		sql.append(indentStr).append("            FULL JOIN (\n");
		sql.append(this.getCostSql(req, indentStr + "                "));
		sql.append(indentStr).append("            ) d USING (").append(groupByColumn).append(")\n");
		sql.append(indentStr).append("    ) t001\n");
		if (CollectionUtils.isNotEmpty(queryColumns) && queryColumns.contains(PlanAttrStatTypeEnum.ADID.V())) {
			sql.append(indentStr).append("    LEFT JOIN (\n");
			sql.append(indentStr).append("        SELECT\n");
			sql.append(indentStr).append("            t050.adid adidTmp, t050.name name, t050.advertiser_id adAccountId, t050.ad_account_name adAccountName, t050.ctype ctype, t050.create_time createTime\n");
			sql.append(indentStr).append("        FROM\n");
			sql.append(indentStr).append("            ").append(yunYingProperties.getPanguadidtable()).append(" t050\n");
			sql.append(indentStr).append("    ) t2000 ON t001.id = t2000.adidTmp\n");
		}
		if (CollectionUtils.isNotEmpty(queryColumns) && queryColumns.contains(PlanAttrStatTypeEnum.PGID.V())) {
			sql.append(indentStr).append("    LEFT JOIN (\n");
			sql.append(indentStr).append("        SELECT\n");
			sql.append(indentStr).append("            t050.id pgId, t050.gname name\n");
			sql.append(indentStr).append("        FROM\n");
			sql.append(indentStr).append("            dim_200_pangu_mysql_parent_game t050\n");
			sql.append(indentStr).append("    ) t2000 ON t001.id = toInt16(t2000.pgId)\n");
		}
		if (CollectionUtils.isNotEmpty(queryColumns) && queryColumns.contains(PlanAttrStatTypeEnum.GAMEID.V())) {
			sql.append(indentStr).append("    LEFT JOIN (\n");
			sql.append(indentStr).append("        SELECT\n");
			sql.append(indentStr).append("            t050.id gameId, t050.gname name\n");
			sql.append(indentStr).append("        FROM\n");
			sql.append(indentStr).append("            dim_200_pangu_mysql_wan_game t050\n");
			sql.append(indentStr).append("    ) t2000 ON t001.id = toInt16(t2000.gameId)\n");
		}
		if (CollectionUtils.isNotEmpty(queryColumns) && queryColumns.contains(PlanAttrStatTypeEnum.PARENTCHL.V())) {
			sql.append(indentStr).append("    LEFT JOIN (\n");
			sql.append(indentStr).append("        SELECT\n");
			sql.append(indentStr).append("            t050.chncode chnCode, t050.chnname name\n");
			sql.append(indentStr).append("        FROM\n");
			sql.append(indentStr).append("            dim_200_pangu_mysql_wan_promotion_channel_v3 t050\n");
			sql.append(indentStr).append("    ) t2000 ON t2000.chnCode = cast(t001.id as varchar)\n");
		}
		if (CollectionUtils.isNotEmpty(queryColumns) && queryColumns.contains(PlanAttrStatTypeEnum.ADVERTISERID.V())) {
			sql.append(indentStr).append("    LEFT JOIN (\n");
			sql.append(indentStr).append("        SELECT\n");
			sql.append(indentStr).append("            t050.advertiser_id advertiser_id, t050.advertiser_name name, t050.media_code ctype\n");
			sql.append(indentStr).append("        FROM\n");
			sql.append(indentStr).append("            dim_200_pangu_mysql_ad_account t050\n");
			sql.append(indentStr).append("    ) t2000 ON t001.id = t2000.advertiser_id\n");
		}
		return sql;
	}


	private StringBuilder getDevicePaySql(PlanAttrAnalyseSearchVo req, final String indentStr) {
		final String groupByColumn = this.convertGroupByColumns(req.getQueryColumn());

		final String day = req.getDate();
		String adMaterialName = req.getAdMaterialName();
		String adStatusArr = req.getAdStatusArr();

		final StringBuilder deviceRegSql = new StringBuilder();

		deviceRegSql.append(indentStr).append("SELECT\n");
		deviceRegSql.append(indentStr).append("    ").append(groupByColumn).append(",\n");
		deviceRegSql.append(indentStr).append("    COUNT(DISTINCT kid) uuidnums,  -- 新增设备注册数\n");
		deviceRegSql.append(indentStr).append("    COUNT(DISTINCT IF(latest_username != '', latest_username, NULL)) usrnamenums, -- 新增设备注册数\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(is_2_retention), 0) retention2, -- 留存人数\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(IF(fee_1 > 0 OR givemoney_1 > 0, 1, 0)), 0) usrpaynamenums, -- 新增设备付费数\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(fee_1), 0) newdevicefees, -- 新增充值实付金额（分成前）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(fee_1 * sharing), 0) newdevicesharfee, -- 新增充值实付金额（分成后）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(givemoney_1), 0) newdevicegivemoney, -- 新增充值代金券金额（分成前）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(givemoney_1 * sharing), 0) newdeviceshargivemoney, -- 新增充值代金券金额（分成后）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(IF(fee_week > 0 OR givemoney_week > 0, 1, 0)), 0) weektotalfeenums, -- 当周充值人数\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(fee_week), 0) weektotalfee, -- 当周充值实付金额（分成前）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(fee_week * sharing), 0) weeksharfee, -- 当周充值实付金额（分成后）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(givemoney_week), 0) weektotalgivemoney, -- 当周充值代金券金额（分成前）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(givemoney_week * sharing), 0) weekshargivemoney, -- 当周充值代金券金额（分成后）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(IF(fee_month > 0 OR givemoney_month > 0, 1, 0)), 0) monthtotalfeenums, -- 当月充值人数\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(fee_month), 0) monthtotalfee, -- 当月充值实付金额（分成前）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(fee_month * sharing), 0) monthsharfee, -- 当月充值实付金额（分成后）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(givemoney_month), 0) monthtotalgivemoney, -- 当月充值代金券金额（分成前）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(givemoney_month * sharing), 0) monthshargivemoney, -- 当月充值代金券金额（分成后）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(IF(fee_total > 0 OR givemoney_total > 0, 1, 0)), 0) totalPayfeenums, -- 累计充值人数\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(fee_total), 0) totalPayfee, -- 累计充值实付金额（分成前）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(fee_total * sharing), 0) totalPaysharfee, -- 累计充值实付金额（分成后）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(givemoney_total), 0) totalPaygivemoney, -- 累计充值代金券金额（分成前）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(givemoney_total * sharing), 0) totalPayshargivemoney -- 累计充值代金券金额（分成后）\n");
		deviceRegSql.append(indentStr).append("FROM\n");
		deviceRegSql.append(indentStr).append("    (\n");
		deviceRegSql.append(indentStr).append("        SELECT\n");
		deviceRegSql.append(indentStr).append("            reg_day day, week, month, year, kid, collect, game_main pgid, os, game_sub gameid, chl_main parentchl, chl_sub chl, chl_base appchl, ad_id adid, ad_account advertiserid, convert_name AS convertName, deep_convert AS deepConvert,\n");
		deviceRegSql.append(indentStr).append("            latest_username, is_2_retention, fee_1, givemoney_1, fee_week, givemoney_week, fee_month, givemoney_month, fee_total, givemoney_total\n");
		deviceRegSql.append(indentStr).append("        FROM\n");
		deviceRegSql.append(indentStr).append("            ").append(yunYingProperties.getNinetydeviceregtable()).append(" reg\n");
		if (StringUtils.isNotBlank(adStatusArr)) {
			deviceRegSql.append(indentStr).append("            INNER JOIN (SELECT adid, status FROM ").append(yunYingProperties.getPanguadidtable()).append(") ad ON reg.ad_id = ad.adid\n");
		}
		if (StringUtils.isNotBlank(adMaterialName)) {
			deviceRegSql.append(indentStr).append("            INNER JOIN (\n");
			deviceRegSql.append(this.filterMaterialNameSqlSnippet(adMaterialName, "                " + indentStr));
			deviceRegSql.append(indentStr).append("            ) oam ON reg.ctype = oam.platform_id AND reg.ad_id = oam.adid\n");
		}
		deviceRegSql.append(indentStr).append("        WHERE\n");
		deviceRegSql.append(indentStr).append("            spread_type = 1 AND reg_day = ").append(day).append(this.directAdvertiseWhereSnippet(req)).append('\n');
		deviceRegSql.append(indentStr).append("    ) reg\n");
		deviceRegSql.append(indentStr).append("    LEFT JOIN (SELECT CAST(id AS Int16) AS id, (1 - COALESCE(sharing, 0)) AS sharing FROM dim_200_pangu_mysql_parent_game) pg ON reg.pgid = pg.id\n");
		deviceRegSql.append(indentStr).append("    LEFT JOIN (SELECT manage investor, parent_code, chncode, dept_id deptId, dept_group_id userGroupId, real_name investorName, dept_name deptName, name userGroupName FROM v_dim_200_pangu_channel_user_dept_group) wpc ON wpc.parent_code = reg.parentchl AND wpc.chncode = reg.chl\n");
		deviceRegSql.append(indentStr).append("WHERE\n");
		deviceRegSql.append(indentStr).append("    1 = 1");
		if (req.getIsSys() != 1) {
			deviceRegSql.append(" AND investor IN (").append(req.getUserIds()).append(')');
		}
		deviceRegSql.append(this.getWhereCommCondSB(req)).append('\n');
		deviceRegSql.append(indentStr).append("GROUP BY\n");
		deviceRegSql.append(indentStr).append("    ").append(groupByColumn).append('\n');

		return deviceRegSql;
	}


	private StringBuilder getCertifiedSql(PlanAttrAnalyseSearchVo req, final String indentStr) {

		final String groupByColumn = this.convertGroupByColumns(req.getQueryColumn());

		final String day = req.getDate();
		String adMaterialName = req.getAdMaterialName();
		String adStatusArr = req.getAdStatusArr();

		StringBuilder certifiedSql = new StringBuilder();

		certifiedSql.append(indentStr).append("SELECT\n");
		certifiedSql.append(indentStr).append("    ").append(groupByColumn).append(",\n");
		certifiedSql.append(indentStr).append("    COALESCE(COUNT(username), 0) AS newUserCount, -- 新增用户数\n");
		certifiedSql.append(indentStr).append("    COALESCE(SUM(createRoleCount), 0) AS createRoleCount, -- 新增注册创角数\n");
		certifiedSql.append(indentStr).append("    COALESCE(SUM(certifiedMark), 0) AS certifiedCount, -- 新增注册实名数\n");
		certifiedSql.append(indentStr).append("    (newUserCount - certifiedCount) AS notCertifiedCount, -- 注册未实名数\n");
		certifiedSql.append(indentStr).append("    COALESCE(SUM(youngMark), 0) AS youngCount -- 未成年人数\n");
		certifiedSql.append(indentStr).append("FROM\n");
		certifiedSql.append(indentStr).append("    (\n");
		certifiedSql.append(indentStr).append("        SELECT\n");
		certifiedSql.append(indentStr).append("            MIN(d_reg_day) day, any(d_reg_week) week, any(d_reg_month) month, any(d_reg_year) year, any(d_kid) kid, any(collect) collect, d_game_main pgid, argMin(d_os, reg_time) os, argMin(d_game_sub, reg_time) gameid,\n");
		certifiedSql.append(indentStr).append("            argMin(d_chl_main, reg_time) parentchl, argMin(d_chl_sub, reg_time) chl, argMin(d_chl_base, reg_time) appchl, argMin(d_ad_id, reg_time) adid, argMin(d_ad_account, reg_time) advertiserid,\n");
		certifiedSql.append(indentStr).append("            argMin(d_convert_name, reg_time) convertName, argMin(d_deep_convert, reg_time) deepConvert, username\n");
		certifiedSql.append(indentStr).append("        FROM\n");
		certifiedSql.append(indentStr).append("            ").append(yunYingProperties.getAccountregistertable()).append(" acc\n");
		if (StringUtils.isNotBlank(adStatusArr)) {
			certifiedSql.append(indentStr).append("            LEFT JOIN (SELECT adid, status FROM ").append(yunYingProperties.getPanguadidtable()).append(") ad ON acc.d_ad_id = ad.adid\n");
		}
		if (StringUtils.isNotBlank(adMaterialName)) {
			certifiedSql.append(indentStr).append("            INNER JOIN (\n");
			certifiedSql.append(this.filterMaterialNameSqlSnippet(adMaterialName, "                " + indentStr));
			certifiedSql.append(indentStr).append("            ) oam ON acc.d_ctype = oam.platform_id AND acc.d_ad_id = oam.adid\n");
		}
		certifiedSql.append(indentStr).append("        WHERE\n");
		certifiedSql.append(indentStr).append("            d_spread_type = 1 AND d_reg_day = ").append(day).append(this.indirectAdvertiseWhereSnippet(req)).append('\n');
		certifiedSql.append(indentStr).append("        GROUP BY\n");
		certifiedSql.append(indentStr).append("            pgid, username\n");
		certifiedSql.append(indentStr).append("    ) acc\n");
		certifiedSql.append(indentStr).append("    LEFT JOIN (\n");
		certifiedSql.append(indentStr).append("        SELECT\n");
		certifiedSql.append(indentStr).append("            toInt16(pgame_id) AS pgame_id, user_name, COUNT(role_id) AS createRoleCount\n");
		certifiedSql.append(indentStr).append("        FROM\n");
		certifiedSql.append(indentStr).append("            (SELECT pgame_id, role_id, argMin(username, create_time) AS user_name FROM v_odsmysql_game_role GROUP BY pgame_id, role_id)\n");
		certifiedSql.append(indentStr).append("        GROUP BY\n");
		certifiedSql.append(indentStr).append("            pgame_id, user_name\n");
		certifiedSql.append(indentStr).append("    ) cr ON acc.pgid = cr.pgame_id AND acc.username = cr.user_name\n");
		certifiedSql.append(indentStr).append("    LEFT JOIN (\n");
		certifiedSql.append(indentStr).append("        SELECT\n");
		certifiedSql.append(indentStr).append("            toInt16(pgid) AS pgame_id, ai user_name, argMax(id_num, check_time) AS id_num, 1 AS certifiedMark, IF(toDate(addYears(parseDateTimeBestEffortOrZero(SUBSTRING(id_num, 7, 8)), 18)) >= today(), 1, 0) youngMark\n");
		certifiedSql.append(indentStr).append("        FROM\n");
		certifiedSql.append(indentStr).append("            v_odsmysql_wan_user_realname\n");
		certifiedSql.append(indentStr).append("        WHERE\n");
		certifiedSql.append(indentStr).append("            status <> 2\n");
		certifiedSql.append(indentStr).append("        GROUP BY\n");
		certifiedSql.append(indentStr).append("            pgame_id, user_name\n");
		certifiedSql.append(indentStr).append("    ) rel ON acc.pgid = rel.pgame_id AND acc.username = rel.user_name\n");
		certifiedSql.append(indentStr).append("    LEFT JOIN (SELECT manage investor, parent_code, chncode, dept_id deptId, dept_group_id userGroupId, real_name investorName, dept_name deptName, COALESCE(name,'-') userGroupName FROM v_dim_200_pangu_channel_user_dept_group) wpc ON wpc.parent_code = acc.parentchl AND wpc.chncode = acc.chl\n");
		certifiedSql.append(indentStr).append("WHERE\n");
		certifiedSql.append(indentStr).append("    1 = 1");
		if (req.getIsSys() != 1) {
			certifiedSql.append(" AND investor IN (").append(req.getUserIds()).append(')');
		}
		certifiedSql.append(this.getWhereCommCondSB(req)).append('\n');
		certifiedSql.append(indentStr).append("GROUP BY\n");
		certifiedSql.append(indentStr).append("    ").append(groupByColumn).append('\n');

		return certifiedSql;
	}


	private StringBuilder getCostSql(PlanAttrAnalyseSearchVo req, final String indentStr) {

		final String groupByColumn = this.convertGroupByColumns(req.getQueryColumn());
		final String day = req.getDate();
		final String adMaterialName = req.getAdMaterialName();
		final String adStatusArr = req.getAdStatusArr();

		final StringBuilder costSql = new StringBuilder();
		costSql.append(indentStr).append("SELECT\n");
		costSql.append(indentStr).append("    ").append(groupByColumn).append(",").append('\n');
		costSql.append(indentStr).append("    SUM(rudeCost) rudeCost,\n");
		costSql.append(indentStr).append("    SUM(cost) cost\n");
		costSql.append(indentStr).append("FROM\n");
		costSql.append(indentStr).append("    (\n");
		costSql.append(indentStr).append("        SELECT\n");
		costSql.append(indentStr).append("            day, collect, adid, convertName, deepConvert, advertiserid, os, pgid, gameid, parentchl, chl, appchl, deptId, userGroupId, userGroupName, investor, investorName,\n");
		costSql.append(indentStr).append("            rudeCost, -- 原始消耗\n");
		costSql.append(indentStr).append("            cost, -- 返点后消耗\n");
		costSql.append(indentStr).append("            clicknums, -- 点击数\n");
		costSql.append(indentStr).append("            shownums, -- 展示数\n");
		costSql.append(indentStr).append("            convertnums -- 转化数\n");
		costSql.append(indentStr).append("        FROM\n");
		costSql.append(indentStr).append("            (\n");
		costSql.append(indentStr).append("                SELECT\n");
		costSql.append(indentStr).append("                    day, week, month, year, collect, convert_name AS convertName, deep_convert AS deepConvert, ad_account AS advertiserid, ad_id adid, ad_show AS shownums, click AS clicknums, rude_cost AS rudeCost, cost, ad_convert AS convertnums\n");
		costSql.append(indentStr).append("                FROM\n");
		costSql.append(indentStr).append("                    ").append(yunYingProperties.getAdidrebatetable()).append(" ard\n");
		if (StringUtils.isNotBlank(adStatusArr)) {
			costSql.append(indentStr).append("                    LEFT JOIN (SELECT adid, status FROM ").append(yunYingProperties.getPanguadidtable()).append(") ad ON ard.ad_id = ad.adid\n");
		}
		if (StringUtils.isNotBlank(adMaterialName)) {
			costSql.append(indentStr).append("                    INNER JOIN (\n");
			costSql.append(this.filterMaterialNameSqlSnippet(adMaterialName, "                        " + indentStr));
			costSql.append(indentStr).append("                    ) oam ON ard.ctype = oam.platform_id AND ard.ad_id = oam.adid\n");
		}
		costSql.append(indentStr).append("                WHERE\n");
		costSql.append(indentStr).append("                    day = ").append(day).append(this.directAdvertiseWhereSnippet(req));
		if (req.getIsSys() != 1) {
			costSql.append(" AND ad_account IN (").append(req.getAdAccounts()).append(")"); // -- 广告权限
		}
		costSql.append('\n');
		costSql.append(indentStr).append("            ) ard\n");
		costSql.append(indentStr).append("            LEFT JOIN (SELECT os, game_main AS pgid, game_sub AS gameid, chl_main AS parentchl, chl_sub AS chl, chl_base AS appchl, ad_id").append(" FROM ").append(yunYingProperties.getAdptypetable()).append(") ap ON ard.adid = ap.ad_id\n");
		costSql.append(indentStr).append("            LEFT JOIN (SELECT parent_code, chncode, dept_id AS deptId, dept_name AS deptName, dept_group_id AS userGroupId, name AS userGroupName, manage AS investor, real_name AS investorName FROM v_dim_200_pangu_channel_user_dept_group) wpc ON wpc.parent_code = ap.parentchl AND wpc.chncode = ap.chl\n");
		costSql.append(indentStr).append("        WHERE\n");
		costSql.append(indentStr).append("            1 = 1").append(this.getWhereCommCondSB(req)).append('\n');
		costSql.append(indentStr).append("    ) b\n");
		costSql.append(indentStr).append("GROUP BY\n");
		costSql.append(indentStr).append("    ").append(groupByColumn).append('\n');
		return costSql;
	}

	private String filterMaterialNameSqlSnippet(String materialName, final String indentStr) {
		if (StringUtils.isBlank(materialName)) {
			return StringUtils.EMPTY;
		}
		return indentStr + "SELECT\n" +
				indentStr + "    toInt8(oacm.platform_id) AS platform_id, toString(oacm.ad_id) AS adid\n" +
				indentStr + "FROM\n" +
				indentStr + "    dim_200_pangu_mysql_ad_creative_material oacm\n" +
				indentStr + "    INNER JOIN dim_200_pangu_mysql_ad_material oam ON oacm.material_id = oam.id\n" +
				indentStr + "WHERE\n" +
				indentStr + "    oam.name LIKE '%" + materialName + "%'\n" +
				indentStr + "GROUP BY\n" +
				indentStr + "    oacm.platform_id, oacm.ad_id\n";
	}

	private String convertGroupByColumns(List<String> queryColumn) {
		final String delimiter = ", ", dayKey = "day";
		if (CollectionUtil.isEmpty(queryColumn)) {
			return dayKey;
		}
		final StringJoiner joiner = new StringJoiner(delimiter);
		joiner.add(dayKey);
		for (String column : queryColumn) {
			joiner.add(column);
			if ("adid".equals(column)) {
				joiner.add("convertName").add("deepConvert");
			}
		}
		return joiner.toString();
	}

	/**
	 * 全局通用过滤条件
	 */
	private String getWhereCommCondSB(PlanAttrAnalyseSearchVo searchVo) {
		final Integer os = searchVo.getOs();
		final String appchlArr = searchVo.getAppchlArr();
		final String parentchlArr = searchVo.getParentchlArr();
		final String investorArr = searchVo.getInvestorArr();
		final String deptIdArr = searchVo.getDeptIdArr();
		final String userGroupIdArr = searchVo.getUserGroupIdArr();
		final String pgidArr = searchVo.getPgidArr();
		final String gameidArr = searchVo.getGameidArr();

		// 通用筛选条件
		final StringBuilder commCondSB = new StringBuilder();
		if (os != null) {
			commCondSB.append(" AND os = ").append(os);
		}
		if (StringUtils.isNotBlank(pgidArr)) {
			commCondSB.append(" AND pgid IN (").append(pgidArr).append(")");
		}
		if (StringUtils.isNotBlank(gameidArr)) {
			commCondSB.append(" AND gameid IN (").append(gameidArr).append(")");
		}
		if (StringUtils.isNotBlank(parentchlArr)) {
			commCondSB.append(" AND parentchl IN ('").append(parentchlArr.replaceAll(Constant.COMMA, "','")).append("')");
		}
		if (StringUtils.isNotBlank(appchlArr)) {
			commCondSB.append(" AND appchl IN ('").append(appchlArr.replaceAll(Constant.COMMA, "','")).append("')");
		}
		if (StringUtils.isNotBlank(deptIdArr)) {
			commCondSB.append(" AND deptId IN (").append(deptIdArr).append(Constant.RIGHT_SMALL_BRANCKET);
		}
		if (StringUtils.isNotBlank(userGroupIdArr)) {
			commCondSB.append(" AND userGroupId IN (").append(userGroupIdArr).append(Constant.RIGHT_SMALL_BRANCKET);
		}
		if (StringUtils.isNotBlank(investorArr)) {
			commCondSB.append(" AND investor IN (").append(investorArr).append(Constant.RIGHT_SMALL_BRANCKET);
		}

		return commCondSB.toString();
	}

	private String directAdvertiseWhereSnippet(PlanAttrAnalyseSearchVo req) {
		final StringBuilder whereSnippet = new StringBuilder();
		final String adStatusArr = req.getAdStatusArr();
		final String convertArr = req.getConvertArr();
		final String deepConvertArr = req.getDeepConvertArr();
		final String convertDataTypeArr = req.getConvertDataTypeArr();

		if (StringUtils.isNotBlank(adStatusArr)) {
			whereSnippet.append(" AND status IN ('").append(adStatusArr.replaceAll(Constant.COMMA, "','")).append("')");
		}

		if (StringUtils.isNotBlank(convertArr)) {
			whereSnippet.append(" AND convert_name IN ('").append(convertArr.replaceAll(Constant.COMMA, "','")).append("')");
		}
		if ("empty".equals(deepConvertArr)) {
			whereSnippet.append(" AND (deep_convert = '' OR deep_convert IS NULL)");
		} else if (StringUtils.isNotBlank(deepConvertArr)) {
			whereSnippet.append(" AND deep_convert = '").append(deepConvertArr).append("'");
		}
		/*if ("empty".equals(convertDataTypeArr)) {
			whereSnippet.append(" AND (convert_data_type = '' OR convert_data_type IS NULL)");
		} else if (StringUtils.isNotBlank(convertDataTypeArr)) {
			whereSnippet.append(" AND convert_data_type = '").append(convertDataTypeArr).append("'");
		}*/

		return whereSnippet.toString();
	}

	private String indirectAdvertiseWhereSnippet(PlanAttrAnalyseSearchVo req) {
		final StringBuilder whereSnippet = new StringBuilder();
		final String adStatusArr = req.getAdStatusArr();
		final String convertArr = req.getConvertArr();
		final String deepConvertArr = req.getDeepConvertArr();
		final String convertDataTypeArr = req.getConvertDataTypeArr();

		if (StringUtils.isNotBlank(adStatusArr)) {
			whereSnippet.append(" AND status IN ('").append(adStatusArr.replaceAll(Constant.COMMA, "','")).append("')");
		}

		if (StringUtils.isNotBlank(convertArr)) {
			whereSnippet.append(" AND d_convert_name IN ('").append(convertArr.replaceAll(Constant.COMMA, "','")).append("')");
		}
		if ("empty".equals(deepConvertArr)) {
			whereSnippet.append(" AND (d_deep_convert = '' OR d_deep_convert IS NULL)");
		} else if (StringUtils.isNotBlank(deepConvertArr)) {
			whereSnippet.append(" AND d_deep_convert = '").append(deepConvertArr).append("'");
		}
		/*if ("empty".equals(convertDataTypeArr)) {
			whereSnippet.append(" AND (d_convert_data_type = '' OR d_convert_data_type IS NULL)");
		} else if (StringUtils.isNotBlank(convertDataTypeArr)) {
			whereSnippet.append(" AND d_convert_data_type = '").append(convertDataTypeArr).append("'");
		}*/

		return whereSnippet.toString();
	}

	/*public String selectDeviceRegCondition(PlanAttrAnalyseSearchVo req, String bieming) {
		final Integer os = req.getOs();
		final String pgidArr = req.getPgidArr();
		final String gameidArr = req.getGameidArr();
		final String appchlArr = req.getAppchlArr();
		final String parentchlArr = req.getParentchlArr();

		// 通用筛选条件
		final StringBuilder commCondSB = new StringBuilder();
		if (os != null) {
			commCondSB.append(" AND os = ").append(os);
		}
		if (StringUtils.isNotBlank(pgidArr)) {
			commCondSB.append(" AND game_main IN (").append(pgidArr).append(")");
		}
		if (StringUtils.isNotBlank(gameidArr)) {
			commCondSB.append(" AND game_sub IN (").append(gameidArr).append(")");
		}
		if (StringUtils.isNotBlank(appchlArr)) {
			commCondSB.append(" AND chl_base IN ('").append(appchlArr.replaceAll(",", "','")).append("')");
		}
		if (StringUtils.isNotBlank(parentchlArr)) {
			commCondSB.append(" AND chl_main IN ('").append(parentchlArr.replaceAll(",", "','")).append("')");
		}

		return commCondSB.toString();
	}*/

}